﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xeml.Document;
using System.Data.SqlClient;
using XemlUtilities.DataSet;
using System.Threading.Tasks;
using System.Data;
using System.Data.Common;
using System.Globalization;
using Mpi.Common.Collections;
using Mpi.Common;
using Xeml.Sampling.Contracts;
using System.Diagnostics;

namespace XemlUtilities.DataSet
{
    public class MSSQLXemlDataSet : XemlDataSet
    {
        public MSSQLXemlDataSet(XemlDocument xemlDocument, IDataProvider dataProvider)
            : base(xemlDocument, dataProvider) {
            Document.Insert = DocumentInsert; // attach the right insert function
            Document.Update = DocumentUpdate; // attach the right insert function
        }

        public MSSQLXemlDataSet(IDataProvider dataProvider)
            :base(dataProvider)
        {
            Document.Insert = DocumentInsert; // attach the right insert function
            Document.Update = DocumentUpdate; // attach the right insert function
        }

        public override bool ExperimenterExists(string email, IDbConnection connection, IDbTransaction transaction)
        {
            using (SqlCommand cmd = (SqlCommand)connection.CreateCommand())
            {
                cmd.CommandText = string.Concat("SELECT @Id = count(*) FROM ", xemlSchemaName, @".Experimenter WHERE Email = @Email;");
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters["@Email"].Direction = ParameterDirection.Input;

                IDbDataParameter idParam = cmd.CreateParameter();
                idParam.ParameterName = "@Id";
                idParam.DbType = DbType.Int32;
                idParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(idParam);

                cmd.Transaction = (SqlTransaction)transaction;
                cmd.ExecuteScalar();

                var Id = cmd.Parameters["@Id"].Value as Int32?;
                if (Id == null) { return false; }
                return Id > 0;
            }
        }

        public override void BulkCopyTable(DataTable dataTable, IDbConnection connection, IDbTransaction trans, bool throwOnDuplicate = true)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints, (SqlTransaction)trans))
            {
                bulkCopy.BatchSize = BulkCopyBatchSize;
                bulkCopy.BulkCopyTimeout = BulkCopyTimeout;
                try
                {
                    bulkCopy.DestinationTableName = string.Concat(xemlSchemaName, ".", dataTable.TableName);

                    foreach (DataColumn col in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }

                    Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "attempt to bulkcopy '{0}'", bulkCopy.DestinationTableName));
                    bulkCopy.WriteToServer(dataTable);
                }
                catch (SqlException ex)
                {
                    if (! throwOnDuplicate && ex.Class == 14 && ex.Number == 2627)
                    {
                        // duplicated key, ignore
                    }
                    else
                    {
                        throw new Exception(string.Format(CultureInfo.InvariantCulture, "SqlException in BulkCopy {0}", bulkCopy.DestinationTableName), ex);
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    bulkCopy.Close();
                }
            }
        }

        protected override IDbCommand GetFillCommand(Guid XemlId, IDbConnection connection)
        {
            SqlCommand cmd = ((SqlConnection)connection).CreateCommand();
            cmd.CommandText = string.Concat(xemlSchemaName, ".GetDataSet");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@XemlId", XemlId);
            return cmd;
        }

        public override void Fill(Guid XemlId)
        {
            using (SqlConnection con = (SqlConnection)_dataProvider.BuildConnection())
            using (SqlCommand cmd = (SqlCommand)GetFillCommand(XemlId, con))
            {
                //try
                //{
                    cmd.Connection.Open();
                    using (cmd.Transaction = cmd.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess))
                    {
                        Fill(reader);
                    }
                //}
                //catch (Exception)
                //{
                //    throw;
                //}
                //finally
                //{
                    cmd.Connection.Close();
                //}
            }
        }

        public IAsyncResult FillAsync(AsyncCallback callback, Guid XemlId)
        {
            using (SqlCommand cmd = (SqlCommand)this.GetFillCommand(XemlId, _dataProvider.BuildConnection()))
            {
                try
                {
                    cmd.Connection.Open();
                    cmd.Transaction = cmd.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
                    return cmd.BeginExecuteReader(callback, cmd, CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess);
                }
                catch (Exception)
                {
                    cmd.Connection.Close();
                    throw;
                }
            }
        }

        #region DataTable specifics
        internal short DocumentInsert(DocumentRow row, IDbConnection connection, IDbTransaction trans)
        {
            using (SqlCommand cmd = (SqlCommand)connection.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = string.Concat(@"INSERT INTO ", xemlSchemaName, @".[Document]
                        (XemlId,Name,StartDate,ObservationTime,Description,ExperimenterEmail,ExperimenterFirstName,ExperimenterLastName,Organisation,NameSpace,XemlVersion,Xeml)
                        VALUES
                        (@XemlId,@Name,@StartDate,@ObservationTime,@Description,@ExperimenterEmail,@ExperimenterFirstName,@ExperimenterLastName,@Organisation,@NameSpace,@XemlVersion,@Xeml);
                        select @Id = Scope_Identity()");
                cmd.Transaction = (SqlTransaction)trans;
                try
                {
                    cmd.Parameters.AddWithValue("@XemlId", row.XemlId);
                    cmd.Parameters.Add("@ExperimenterEmail", System.Data.SqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterFirstName", System.Data.SqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterLastName", System.Data.SqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@Organisation", System.Data.SqlDbType.VarChar, 255);

                    if (row.IsExperimenterEmailNull())
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = row.ExperimenterEmail;
                    }
                    if (row.IsExperimenterFirstNameNull())
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = row.ExperimenterFirstName;
                    }
                    if (row.IsExperimenterLastNameNull())
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = row.ExperimenterLastName;
                    }
                    if (row.IsOrganisationNull())
                    {
                        cmd.Parameters["@Organisation"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@Organisation"].Value = row.Organisation;
                    }

                    cmd.Parameters.AddWithValue("@Name", row.Name);
                    cmd.Parameters.AddWithValue("@StartDate", row.StartDate);
                    cmd.Parameters.AddWithValue("@ObservationTime", row.ObservationTime);
                    cmd.Parameters.AddWithValue("@Description", row.Description);
                    cmd.Parameters.AddWithValue("@NameSpace", XemlDocument.xmlNameSpace);
                    cmd.Parameters.AddWithValue("@XemlVersion", XemlDocument.XemlVersion);
                    cmd.Parameters.AddWithValue("@Xeml", row.Xeml);

                    foreach (SqlParameter item in cmd.Parameters)
                    {
                        item.Direction = System.Data.ParameterDirection.Input;
                    }

                    cmd.Parameters.Add("@Id", System.Data.SqlDbType.SmallInt);
                    cmd.Parameters["@Id"].Direction = System.Data.ParameterDirection.Output;

                    Debug.WriteLine("attempt to insert into Document table");
                    cmd.ExecuteNonQuery();

                    return Convert.ToInt16(cmd.Parameters["@Id"].Value);
                }
                catch (SqlException ex)
                {
                    if (ex.Number == 2601 && ex.Class == 14) // Cannot insert duplicate key row in object error
                    {
                        if (ex.Message.Contains("IX_UniqueXemlDocumentName"))
                        {
                            // handle duplicate key error in index 'IX_UniqueXemlDocumentName'
                            throw new Exception(string.Format("The XEML store does not allow duplicated document names in table {0}.Document! There is already an experiment with the name '{1}'. Change the experiment name or delete the old experiment from the XEML store before reattempting to upload this experiment.", xemlSchemaName, row.Name), ex);
                        }
                        // here we could handle other duplicate key errors in other indexes as well
                    }
                    throw new Exception("SqlException in Document.Insert()", ex);
                }
                catch (Exception ex)
                {
                    throw new Exception("Exception in Document.Insert()", ex);
                }
                finally
                {
                }
            }
        }
        internal void DocumentUpdate(DocumentRow row, IDbConnection connection, IDbTransaction trans, short internalId)
        {
            using (SqlCommand cmd = (SqlCommand)connection.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = string.Concat("Update ", xemlSchemaName, @".[Document] Set
                            [XemlId] = @XemlId,
                            [Name] = @Name,
                            [StartDate] = @StartDate,
                            [ObservationTime] = @ObservationTime,
                            [Description] = @Description,
                            ExperimenterEmail = @ExperimenterEmail,
                            ExperimenterFirstName = @ExperimenterFirstName,
                            ExperimenterLastName = @ExperimenterLastName,
                            Organisation = @Organisation,
                            [NameSpace] = @NameSpace,
                            [XemlVersion] =@XemlVersion,
                            [Xeml] = @Xeml,
                            [DATE] = getdate(),
                            [USER_NAME] = USER_NAME() WHERE [id] = @id;");
                cmd.Transaction = (SqlTransaction)trans;
                try
                {
                    cmd.Parameters.AddWithValue("@id", internalId);
                    cmd.Parameters.AddWithValue("@XemlId", row.XemlId);

                    cmd.Parameters.Add("@ExperimenterEmail", System.Data.SqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterFirstName", System.Data.SqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterLastName", System.Data.SqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@Organisation", System.Data.SqlDbType.VarChar, 255);

                    if (row.IsExperimenterEmailNull())
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = row.ExperimenterEmail;
                    }
                    if (row.IsExperimenterFirstNameNull())
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = row.ExperimenterFirstName;
                    }
                    if (row.IsExperimenterLastNameNull())
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = row.ExperimenterLastName;
                    }
                    if (row.IsOrganisationNull())
                    {
                        cmd.Parameters["@Organisation"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@Organisation"].Value = row.Organisation;
                    }

                    cmd.Parameters.AddWithValue("@Name", row.Name);
                    cmd.Parameters.AddWithValue("@StartDate", row.StartDate);
                    cmd.Parameters.AddWithValue("@ObservationTime", row.ObservationTime);
                    if (row.IsDescriptionNull())
                    {
                        cmd.Parameters.AddWithValue("@Description", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@Description", row.Description);
                    }
                    cmd.Parameters.AddWithValue("@NameSpace", XemlDocument.xmlNameSpace);
                    cmd.Parameters.AddWithValue("@XemlVersion", XemlDocument.XemlVersion);
                    cmd.Parameters.AddWithValue("@Xeml", row.Xeml);

                    foreach (SqlParameter item in cmd.Parameters)
                    {
                        item.Direction = System.Data.ParameterDirection.Input;
                    }

                    Debug.WriteLine("attempt to update into Document table");
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("Exception in Document.Update()", ex);
                }
                finally
                {
                }
            }
        }
        #endregion DataTable specifics


        #region SQL specifics
        public override string GetUserNameFunction()
        {
            return "USER_NAME()";
        }
        #endregion SQL specifics
    }
}
